﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using Jenzabar.Portal.Framework.Web.UI;
using Jenzabar.Portal.Framework;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;//Req For PortletViewBase

namespace Portlet.AURoster
{
    public partial class Default_View : PortletViewBase
    {
        string cid, exportSql;  
        DataTable exportTable = new DataTable();
      
        protected void Page_Load(object sender, EventArgs e)
        {
           
            lblCoach.Text = PortalUser.Current.FirstName + " " + PortalUser.Current.LastName;
            lblDate.Text = DateTime.Now.ToShortDateString();
            //if (!IsPostBack)
            //{
                ListActivePlayer(gvPlayer);
            //}

            
        }

        private void ListActivePlayer(GridView gv)
        {
            using (SqlConnection myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["NotepadConnectionString"].ConnectionString))
            {
               
                try
                {
                    myConn.Open();
                    cid = PortalUser.Current.HostID;
                    SqlCommand cmd = new SqlCommand("Select Student_ID,   Last_Name, First_Name, No_of_Competed, NAIA_EC#,HS_OR_College,HS_GRAD_DATE ,  [Status],  [TIMESTAMP] from [TmsEprd].[dbo].[CUS_AURoster_Team] WHERE [Coach_ID] ='" + cid + "'  order by  Last_Name ", myConn);
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet();

                    da.Fill(ds);

                    gv.DataSource = ds;
                    gv.DataBind();


                }
                catch (Exception ex)
                {


                    this.ParentPortlet.ShowFeedback(FeedbackType.Error, "Error retreiving Data Items: " + ex.Message);
                }
                finally
                {
                    // Close Connection
                    myConn.Close();
                }
        }
        }

        protected void btnNew_Click(object sender, EventArgs e)
        {
            this.ParentPortlet.NextScreen("New");
        }

        
        protected void btnExport_Click(object sender, EventArgs e)
        {
            cid = PortalUser.Current.HostID;
            string header;// = "Coach : " + PortalUser.Current.FirstName +" " + PortalUser.Current.LastName ;

            string exportHeader = "Select Team = ad.ATTRIBUTE_DESC,  Coach =  cac.[Coach]  "; //,cac.Coach_Phone  ";
             exportHeader +=  " from [TmsEprd].[dbo].[CUS_AURoster_Team] cat ";
             exportHeader +=  " inner join TmsEprd.dbo.CUS_AURoster_Coach  cac on cac.Coach_ID = cat.Coach_ID ";
             exportHeader += " inner join TmsEprd.dbo.ATTRIBUTE_DEF ad on ad.ATTRIBUTE_CDE = cac.Team ";
             exportHeader +=  "WHERE cac.[Coach_ID] ='"+ cid + "'" ;

             exportSql = "Select First_Name, Last_Name , SSN, [Status],No_of_Competed, NAIA_EC#,HS_OR_College,HS_GRAD_DATE , cat.[TIMESTAMP] ";
             exportSql += " from [TmsEprd].[dbo].[CUS_AURoster_Team] cat ";
             exportSql += " inner join TmsEprd.dbo.CUS_AURoster_Coach  cac on cac.Coach_ID = cat.Coach_ID ";
             exportSql += " inner join TmsEprd.dbo.ATTRIBUTE_DEF ad on ad.ATTRIBUTE_CDE = cac.Team ";
             exportSql += "WHERE cac.[Coach_ID] ='" + cid + "'" + " Order by Last_Name "; 

            
            
            
            
            
            using (SqlConnection myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["NotepadConnectionString"].ConnectionString))
             {
                 try
                 {
                     myConn.Open();
                     SqlCommand hdr = new SqlCommand(exportHeader, myConn);
                  
                    

                         SqlDataAdapter da = new SqlDataAdapter(exportSql, myConn);
                         da.Fill(exportTable);
                         string attachment = "attachment; filename=" + cid + " AURoster.xls";

                         Response.ClearContent();

                         Response.AddHeader("content-disposition", attachment);

                         Response.ContentType = "application/vnd.ms-excel";
                         SqlDataReader rdr;
                         rdr = hdr.ExecuteReader();
                         while (rdr.Read())
                         {

                             header = "Coach : " + rdr["Coach"].ToString() + "   " + "Team :" + rdr["Team"].ToString();
                             Response.Write(header);
                             
                        
                       
                        
                         Response.Write("\n");


                         string tab = "";

                         foreach (DataColumn dc in exportTable.Columns)
                         {

                             Response.Write(tab + dc.ColumnName);

                             tab = "\t";

                         }

                         Response.Write("\n");



                         int i;

                         foreach (DataRow dr in exportTable.Rows)
                         {

                             tab = "";

                             for (i = 0; i < exportTable.Columns.Count; i++)
                             {

                                 Response.Write(tab + dr[i].ToString());

                                 tab = "\t";

                             }

                             Response.Write("\n");

                         }

                         Response.End();
                         }
                         rdr.Close();

                 }
                 catch(Exception ex)
                 {

                     this.ParentPortlet.ShowFeedback(FeedbackType.Error, "Error Exporting Data Items: " + ex.Message);
                 }
                 finally
                 {
                     // Close Connection
                     myConn.Close();
                 }
             }
        }

     
        private void populateDD(DropDownList dd)
        {
            List<String> lstStr = new List<String>();
            lstStr.Add("New FR");
            lstStr.Add("New TR");
            lstStr.Add("Return");
            lstStr.Add("Delete");
            dd.DataSource = lstStr;
            dd.DataBind();
        }

        protected void lnkSearch_Click(object sender, EventArgs e)
        {
            this.ParentPortlet.NextScreen("Search");
        }

        
        private void updateFunction(string sid, string fname, string lname )//, string competed, string naia, string hs, string gdate, string dd, string cb)
        {
            using (SqlConnection myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["NotepadConnectionString"].ConnectionString))
            {

                try
                {
                    myConn.Open();

                    string updateSql = " UPDATE [TmsEprd].[dbo].[CUS_AURoster_Team]";

                    updateSql += "SET  [First_Name] = '" + fname + "'";
                    updateSql += ",[Last_Name] =  '" + lname + "'";
                    updateSql += "WHERE [Student_ID] = '" + sid  + "'";
                    SqlCommand cmd = new SqlCommand(updateSql, myConn);
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();

        //        
                    //SqlCommand cmd = new SqlCommand("CUS_sp_AURoster_Player_Update", myConn);
                    //cmd.Parameters.Add("@cid", SqlDbType.VarChar, 10).Value = PortalUser.Current.HostID;

                    //cmd.Parameters.Add("@sid", SqlDbType.VarChar, 10).Value = sid;
                    //cmd.Parameters.Add("@first", SqlDbType.VarChar, 100).Value = fname;
                    //cmd.Parameters.Add("@last", SqlDbType.VarChar, 100).Value = lname;
                    //cmd.Parameters.Add("@status", SqlDbType.VarChar, 100).Value = dd;
                    //cmd.Parameters.Add("@compete", SqlDbType.VarChar, 100).Value = competed;
                    //cmd.Parameters.Add("@hs", SqlDbType.VarChar, 100).Value = hs;
                    //cmd.Parameters.Add("@gdate", SqlDbType.VarChar, 20).Value = gdate ;
                    //cmd.Parameters.Add("@ec#", SqlDbType.VarChar, 10).Value = naia;
                    //cmd.Parameters.Add("@active", SqlDbType.VarChar, 10).Value = cb;

                    //cmd.CommandType = CommandType.StoredProcedure;
                    //cmd.ExecuteNonQuery();
                    //Response.Write(competed.Text);
                }
                catch (Exception ex)
                {


                    this.ParentPortlet.ShowFeedback(FeedbackType.Error, "Error Inserting Data Items: " + ex.Message);
                }
                finally
                {
                    // Close Connection
                    myConn.Close();
                }
            }


                               
        }

        private void BindGridData(string UpdateQuery)
        {
            cid = PortalUser.Current.HostID;
            using (SqlConnection myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["NotepadConnectionString"].ConnectionString))
            {

                try
                {

                    myConn.Open();
                    SqlCommand cmd = new SqlCommand(UpdateQuery, myConn);
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
                    ListActivePlayer(gvPlayer);

                }
                catch (Exception ex)
                {
                    this.ParentPortlet.ShowFeedback(FeedbackType.Error, "Error Updating Items: " + ex.Message);

                }
                finally
                {
                    myConn.Close();

                }


            }
        }

        protected void gvPlayer_SelectedIndexChanged(object sender, EventArgs e)
        {
            GridViewRow row = gvPlayer.SelectedRow;

        }

        protected void gvPlayer_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {

        }

        protected void gvPlayer_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            // For Paging 
            gvPlayer.Visible = true;
            gvPlayer.PageIndex = e.NewPageIndex;
            ListActivePlayer(gvPlayer);

        }



        
    }
}

//protected void gvPlayer_PageIndexChanging(object sender, GridViewPageEventArgs e)
//{
//    gvPlayer.PageIndex = e.NewPageIndex;
//   ListActivePlayer(gvPlayer);
//}

//protected void gvPlayer_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
//{
//    gvPlayer.EditIndex = -1;
//    ListActivePlayer(gvPlayer);
//}

//protected void gvPlayer_RowEditing(object sender, GridViewEditEventArgs e)
//{
//    gvPlayer.EditIndex = e.NewEditIndex;
//    ListActivePlayer(gvPlayer);
//}

//protected void gvPlayer_RowUpdating(object sender, GridViewUpdateEventArgs e)
//{
//    //int index = gvPlayer.EditIndex;
//    ////GridViewRow row = gvPlayer.Rows[index];
//    //gvPlayer.SelectedIndex = e.RowIndex;
//    GridViewRow selectedRow = gvPlayer.SelectedRow;
//    //lp = selectedRow.Cells[6].Text;
//    // Get the controls that contain the updated values. In this
//    // example, the updated values are contained in the TextBox
//    // controls declared in the edit item templates of each TemplateField
//    // column fields in the GridView control.
//    TextBox sid = (TextBox)gvPlayer.SelectedRow.Cells[1].FindControl("txtId");
//    TextBox fname = (TextBox)gvPlayer.SelectedRow.Cells[0].FindControl("txtFname");
//    TextBox lname = (TextBox)gvPlayer.SelectedRow.Cells[2].FindControl("txtLname");
//    TextBox competed = (TextBox)gvPlayer.SelectedRow.Cells[3].FindControl("txtCompete");
//    TextBox naia = (TextBox)gvPlayer.SelectedRow.Cells[4].FindControl("txtNAIA");
//    TextBox hs = (TextBox)gvPlayer.SelectedRow.Cells[5].FindControl("txtHS");
//    TextBox gdate = (TextBox)gvPlayer.SelectedRow.Cells[6].FindControl("txtDate");
//    DropDownList dd = (DropDownList)gvPlayer.SelectedRow.Cells[7].FindControl("ddStatus");
//    CheckBoxList cb = (CheckBoxList)gvPlayer.SelectedRow.Cells[8].FindControl("cbActive");



//    //Label sid = (Label)gvPlayer.Rows[e.RowIndex].FindControl("lblSId");
//    //TextBox fname = (TextBox)gvPlayer.Rows[e.RowIndex].FindControl("txtFname");
//    //TextBox lname = (TextBox)gvPlayer.Rows[e.RowIndex].FindControl("txtLname");
//    //TextBox competed = (TextBox)gvPlayer.Rows[e.RowIndex].FindControl("txtCompete");
//    //TextBox naia = (TextBox)gvPlayer.Rows[e.RowIndex].FindControl("txtNAIA");
//    //TextBox hs = (TextBox)gvPlayer.Rows[e.RowIndex].FindControl("txtHS");
//    //TextBox gdate = (TextBox)gvPlayer.Rows[e.RowIndex].FindControl("txtDate");
//    //DropDownList dd = (DropDownList)gvPlayer.Rows[e.RowIndex].FindControl("ddStatus");
//    //CheckBoxList cb = (CheckBoxList)gvPlayer.Rows[e.RowIndex].FindControl("cbActive");
//    using (SqlConnection myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["NotepadConnectionString"].ConnectionString))
//    {
//        try
//        {
//            myConn.Open();
//            string updateSql = " UPDATE [TmsEprd].[dbo].[CUS_AURoster_Team]";

//            updateSql += "SET  [First_Name] = '" + fname.Text + "'";
//            updateSql += ",[Last_Name] =  '" + lname.Text + "'";
//            updateSql += ",[Status] =  '" + dd.SelectedValue.ToString() + "'";
//            updateSql += ",[No_of_Competed] =  " + Convert.ToInt32(competed.Text);
//            updateSql += ",[HS_OR_College] = '" + hs.Text + "'";
//            updateSql += ",[HS_GRAD_DATE] =  '" + gdate.Text + "'";
//            updateSql += ",[NAIA_EC#] =  '" + naia.Text + "'";
//            updateSql += ",[TIMESTAMP] = GETDATE() ";
//            updateSql += ",[Active] =  '" + cb.SelectedValue.ToString() + "'";
//            updateSql += "WHERE [Student_ID] = '" + sid.Text  + "'";
//            SqlCommand cmd = new SqlCommand(updateSql, myConn);
//            cmd.CommandType = CommandType.Text;
//            cmd.ExecuteNonQuery();

//        }

//        catch (SqlException ex)
//        {
//            this.ParentPortlet.ShowFeedback(FeedbackType.Error, "Error Updating Items: " + ex.Message);

//        }
//        finally
//        {
//            myConn.Close();

//        }
//    }
//    // Reset edit mode
//    gvPlayer.EditIndex = -1;
//    ListActivePlayer(gvPlayer);

//}



//protected void gvPlayer_RowDataBound(object sender, GridViewRowEventArgs e)
//{
//    DataRowView drv = e.Row.DataItem as DataRowView;
//    if (e.Row.RowType == DataControlRowType.DataRow)
//    {
//        if ((e.Row.RowState & DataControlRowState.Edit) > 0)
//        {
//            Label sid = (Label)e.Row.FindControl("id");
//            sid.Text = drv[1].ToString();

//            //TextBox first = (TextBox)e.Row.FindControl("txtFname");
//            //first.Text = drv[2].ToString();
//            //TextBox last = (TextBox)e.Row.FindControl("txtLname");
//            //last.Text  = drv[3].ToString();

//            //TextBox compete = (TextBox)e.Row.FindControl("txtCompete");
//            //compete.Text = drv[4].ToString();
//            //TextBox naia = (TextBox)e.Row.FindControl("txtNAIA");
//            //naia.Text = drv[5].ToString();

//            //TextBox hs = (TextBox)e.Row.FindControl("txtHS");
//            //hs.Text = drv[6].ToString();
//            //TextBox gdate = (TextBox)e.Row.FindControl("txtDate");
//            //gdate.Text = drv[7].ToString();

//            DropDownList dp = (DropDownList)e.Row.FindControl("ddStatus");
//            populateDD(dp);

//            dp.SelectedValue = drv[8].ToString(); // Position of status column in gridView(starts from "0")

//            CheckBoxList chkb = (CheckBoxList)e.Row.FindControl("CBActive");
//            chkb.SelectedValue = drv[9].ToString(); // Array Position of Active column in gridView(starts from "0")

//            //Also add Radiobuttons like below
//            //RadioButtonList rbtnl = (RadioButtonList)e.Row.FindControl("RadioButtonList1");
//            //rbtnl.SelectedValue = drv[5].ToString();
//        }



//    }
//}

